
/**
	AUTHOR		: VEERU
	DATE		: FEB 10th 2004
	DESCRIPTION	: THIS FUNCTION IS USED TO FETCH ALL THE SELECTED PRODUCTS FOR PROJECT.
*/

ALTER FUNCTION FN_LOAD_PRODUCTS_FOR_PROJECT( @PROJID INT, @PART_NUMBER_TYPE VARCHAR(20) ) 

RETURNS @FN_RESULTS TABLE ( PRODID INT, PART_NUMBER VARCHAR(50), P_MARKETING_PART_NUMBER VARCHAR(50))

AS 
BEGIN

	DECLARE @RESULTS TABLE ( PRODID INT, PART_NUMBER VARCHAR(50), P_MARKETING_PART_NUMBER VARCHAR(50) )
	
	-- RETRIEVING AVAILABLE CHIPS FOR PROJECT
	IF ( ( SELECT COUNT(*) FROM PROJPRODREL A, PRODUCT B WHERE PROJID = @PROJID AND A.PRODID = B.PRODUCT_ID AND P_TYPE IS NULL ) > 0 )	
	BEGIN
		INSERT INTO @RESULTS ( PRODID, PART_NUMBER, P_MARKETING_PART_NUMBER ) VALUES ( 0, '---CHIP---', '---CHIP---' )
	END	
	IF( @PART_NUMBER_TYPE = 'PART_NUMBER' )
	BEGIN
		INSERT INTO @RESULTS  SELECT * FROM (
			SELECT PRODID, PART_NUMBER, P_MARKETING_PART_NUMBER FROM PROJPRODREL A, PRODUCT B WHERE PROJID = @PROJID AND A.PRODID = B.PRODUCT_ID AND P_TYPE IS NULL 
			UNION SELECT PRODID, PART_NUMBER, P_MARKETING_PART_NUMBER FROM FCENTRYMASTER A, PRODUCT B WHERE PROJID = @PROJID AND A.PRODID = B.PRODUCT_ID AND P_TYPE IS NULL )
			AS TEMPTABLE ORDER BY PART_NUMBER  
	END	
	ELSE
	BEGIN
		INSERT INTO @RESULTS SELECT * FROM (
			SELECT PRODID, PART_NUMBER, P_MARKETING_PART_NUMBER FROM PROJPRODREL A, PRODUCT B WHERE PROJID = @PROJID AND A.PRODID = B.PRODUCT_ID AND P_TYPE IS NULL  
			UNION SELECT PRODID, PART_NUMBER, P_MARKETING_PART_NUMBER FROM FCENTRYMASTER A, PRODUCT B WHERE PROJID = @PROJID AND A.PRODID = B.PRODUCT_ID AND P_TYPE IS NULL )
			AS TEMPTABLE ORDER BY P_MARKETING_PART_NUMBER				
	END


	-- RETRIEVING AVAILABLE BOARDS FOR PROJECT	
	IF( ( SELECT COUNT(*) FROM PROJPRODREL A, PRODUCT B WHERE PROJID = @PROJID AND A.PRODID = B.PRODUCT_ID AND P_TYPE = 'BOARD' ) > 0 )
	BEGIN
		INSERT INTO @RESULTS ( PRODID, PART_NUMBER, P_MARKETING_PART_NUMBER ) VALUES ( 0, '---BOARD---', '---BOARD---' )
	END	
	IF( @PART_NUMBER_TYPE = 'PART_NUMBER' )
	BEGIN	
		INSERT INTO @RESULTS SELECT * FROM (
			SELECT PRODID, PART_NUMBER, P_MARKETING_PART_NUMBER FROM PROJPRODREL A, PRODUCT B WHERE PROJID = @PROJID AND A.PRODID = B.PRODUCT_ID AND P_TYPE = 'BOARD' 
			UNION SELECT PRODID, PART_NUMBER, P_MARKETING_PART_NUMBER FROM FCENTRYMASTER A, PRODUCT B WHERE PROJID = @PROJID AND A.PRODID = B.PRODUCT_ID AND P_TYPE = 'BOARD' )
			AS TEMPTABLE ORDER BY P_MARKETING_PART_NUMBER 
	END
	ELSE
	BEGIN
		INSERT INTO @RESULTS SELECT * FROM (
			SELECT PRODID, PART_NUMBER, P_MARKETING_PART_NUMBER FROM PROJPRODREL A, PRODUCT B WHERE PROJID = @PROJID AND A.PRODID = B.PRODUCT_ID AND P_TYPE = 'BOARD' 
			UNION SELECT PRODID, PART_NUMBER, P_MARKETING_PART_NUMBER FROM FCENTRYMASTER A, PRODUCT B WHERE PROJID = @PROJID AND A.PRODID = B.PRODUCT_ID AND P_TYPE = 'BOARD' )
			AS TEMPTABLE ORDER BY P_MARKETING_PART_NUMBER 
	END


	-- RETRIEVING AVAILABLE KITS FOR PROJECT	
	IF( ( SELECT COUNT(*) FROM PROJPRODREL A, PRODUCT B WHERE PROJID = @PROJID AND A.PRODID = B.PRODUCT_ID AND P_TYPE = 'KIT' ) > 0 )
	BEGIN
		INSERT INTO @RESULTS ( PRODID, PART_NUMBER, P_MARKETING_PART_NUMBER ) VALUES ( 0, '---KIT---', '---KIT---' )
	END
	IF( @PART_NUMBER_TYPE = 'PART_NUMBER' )
	BEGIN	
		INSERT INTO @RESULTS  SELECT * FROM (
			SELECT PRODID, PART_NUMBER, P_MARKETING_PART_NUMBER FROM PROJPRODREL A, PRODUCT B WHERE PROJID = @PROJID AND A.PRODID = B.PRODUCT_ID AND P_TYPE = 'KIT' 
			UNION SELECT PRODID, PART_NUMBER, P_MARKETING_PART_NUMBER FROM FCENTRYMASTER A, PRODUCT B WHERE PROJID = @PROJID AND A.PRODID = B.PRODUCT_ID AND P_TYPE = 'KIT' )
			AS TEMPTABLE ORDER BY PART_NUMBER 	
	END
	ELSE
	BEGIN
		INSERT INTO @RESULTS  SELECT * FROM (
			SELECT PRODID, PART_NUMBER, P_MARKETING_PART_NUMBER FROM PROJPRODREL A, PRODUCT B WHERE PROJID = @PROJID AND A.PRODID = B.PRODUCT_ID AND P_TYPE = 'KIT'
			UNION SELECT PRODID, PART_NUMBER, P_MARKETING_PART_NUMBER FROM FCENTRYMASTER A, PRODUCT B WHERE PROJID = @PROJID AND A.PRODID = B.PRODUCT_ID AND P_TYPE = 'KIT' )
			AS TEMPTABLE ORDER BY P_MARKETING_PART_NUMBER 	
	END

	
	INSERT INTO @FN_RESULTS SELECT * FROM @RESULTS
	RETURN
END






